
package com.rasenhund.android.grecorder.dao;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import android.content.ContentValues;
import android.database.sqlite.SQLiteCursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.rasenhund.android.grecorder.common.Const;
import com.rasenhund.android.grecorder.schema.CategoryCount;
import com.rasenhund.android.grecorder.schema.Record;
import com.rasenhund.android.grecorder.schema.Stage;

/**
 * 戦闘履歴 Data Access Objectクラス
 */
public class RecordDao {
    // @SuppressWarnings("unused")
    private static final String TAG = RecordDao.class.getSimpleName();
    // private final BattleRecordDao self = this;

    private SQLiteDatabase mDatabase;

    // テーブル名
    public static final String TABLE_NAME = "BattleRecord";

    // カラム名
    public class Columns {
        public static final String ROW_ID = "_id";
        public static final String STAGE_CD = "stageCd";
        public static final String CATEGORY_CD = "categoryCd";
        public static final String RESULT_CD = "resultCd";
        public static final String INSERT_DATE = "insertDate";
    }

    /**
     * Constractor
     * 
     * @param db
     */
    public RecordDao(SQLiteDatabase db) {
        mDatabase = db;
    }

    /**
     * Insert
     * 
     * @param record
     * @return
     */
    public long insert(Record record) {

        Date date = new Date();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
        String sDate = sdf.format(date);

        ContentValues values = new ContentValues();
        // values.put(Columns.ROW_ID, record.getRowId());
        values.put(Columns.STAGE_CD, record.getStageCd());
        values.put(Columns.CATEGORY_CD, record.getCategoryCd());
        values.put(Columns.RESULT_CD, record.getResultCd());
        values.put(Columns.INSERT_DATE, sDate);

        return mDatabase.insert(TABLE_NAME, null, values);

    }

    /**
     * 全件取得
     * 
     * @return
     */
    public ArrayList<Stage> selectAll() {

        ArrayList<Stage> list = new ArrayList<Stage>();
        Stage stage = null;

        SQLiteCursor cursor = null;

        String[] columns = {
                Columns.ROW_ID,
                Columns.STAGE_CD, 
                Columns.CATEGORY_CD, 
                Columns.RESULT_CD, 
                Columns.INSERT_DATE
        };
        // String sOrderBy = Columns.ROW_ID + " DESC LIMIT 20";
        String sOrderBy = Columns.ROW_ID + " DESC";

        try {

            cursor = (SQLiteCursor) mDatabase.query(TABLE_NAME, columns, null, null, null, null,
                    sOrderBy);
            cursor.moveToFirst();

            if (cursor.getCount() > 0) {

                for (int i = 0; i < cursor.getCount(); i++) {

                    stage = new Stage(cursor.getInt(1));

                    stage.setRowId(cursor.getLong(0));
                    // stage.setStageId(cursor.getInt(1));
                    stage.setCategoryCd(cursor.getInt(2));
                    stage.setResultCd(cursor.getInt(3));
                    stage.setInsertDate(cursor.getString(4));

                    list.add(stage);

                    cursor.moveToNext();

                }

            }

        } catch (Exception e) {

            Log.e(TAG, "selectAll failed - " + e.toString());

        } finally {

            if (cursor != null) {
                cursor.close();
            }

        }

        return list;

    }

    /**
     * 戦績データの取得
     * 
     * @param stage
     */
    public void getResult(Stage stage) {

        SQLiteCursor cursor = null;
        int iStageId = stage.getStageId();

        // @formatter:off
        String sQuery = 
            " select " + 
            " COUNT(" + Columns.ROW_ID + ") " +
            " , SUM( CASE " + Columns.RESULT_CD + " WHEN " + Const.RESULT.WIN + " THEN 1 ELSE 0 END ) " + 
            " , SUM( CASE " + Columns.RESULT_CD + " WHEN " + Const.RESULT.LOSE + " THEN 1 ELSE 0 END ) " + 
            " , SUM( CASE " + Columns.RESULT_CD + " WHEN " + Const.RESULT.DRAW + " THEN 1 ELSE 0 END ) " + 
            "   from " + TABLE_NAME + 
            "  where " + Columns.STAGE_CD + " = " + iStageId + 
            "  group by " + Columns.STAGE_CD;
        // @formatter:on

        try {

            cursor = (SQLiteCursor) mDatabase.rawQuery(sQuery, null);
            cursor.moveToFirst();

            if (cursor.getCount() > 0) {

                stage.setTotalCount(cursor.getInt(0));
                stage.setWinCount(cursor.getInt(1));
                stage.setLoseCount(cursor.getInt(2));
                stage.setDrawCount(cursor.getInt(3));

            }

        } catch (Exception e) {

            Log.e(TAG, "getResult failed - " + e.toString());

        } finally {

            if (cursor != null) {
                cursor.close();
            }

        }

        return;

    }

    /**
     * ステージ毎、カテゴリ毎の戦績データの取得
     * 
     * @param stage
     */
    public ArrayList<Stage> getResultByCategory(int stageId) {

        // TODO:
        ArrayList<Stage> list = new ArrayList<Stage>();
        Stage stage = null;

        SQLiteCursor cursor = null;

        // @formatter:off
        String sQuery = 
            " select " + Columns.CATEGORY_CD + 
            " , COUNT(" + Columns.ROW_ID + ") " +
            " , SUM( CASE " + Columns.RESULT_CD + " WHEN " + Const.RESULT.WIN + " THEN 1 ELSE 0 END ) " + 
            " , SUM( CASE " + Columns.RESULT_CD + " WHEN " + Const.RESULT.LOSE + " THEN 1 ELSE 0 END ) " + 
            " , SUM( CASE " + Columns.RESULT_CD + " WHEN " + Const.RESULT.DRAW + " THEN 1 ELSE 0 END ) " + 
            "   from " + TABLE_NAME + 
            "  where " + Columns.STAGE_CD + " = " + stageId + 
            "  group by " + Columns.STAGE_CD + ", " + Columns.CATEGORY_CD;
        // @formatter:on

        try {

            cursor = (SQLiteCursor) mDatabase.rawQuery(sQuery, null);
            cursor.moveToFirst();

            if (cursor.getCount() > 0) {

                for (int i = 0; i < cursor.getCount(); i++) {

                    stage = new Stage(stageId);

                    stage.setCategoryCd(cursor.getInt(0));
                    stage.setTotalCount(cursor.getInt(1));
                    stage.setWinCount(cursor.getInt(2));
                    stage.setLoseCount(cursor.getInt(3));
                    stage.setDrawCount(cursor.getInt(4));

                    list.add(stage);

                    cursor.moveToNext();

                }

            }

        } catch (Exception e) {

            Log.e(TAG, "getResultByCategory failed - " + e.toString());

        } finally {

            if (cursor != null) {
                cursor.close();
            }

        }

        return list;

    }

    /**
     * カテゴリ別出撃回数データの取得
     * 
     * @return
     */
    public CategoryCount getCategoryCount() {
        return getCategoryCount(0);
    }


    /**
     * カテゴリ別出撃回数データの取得(先頭n件)
     * 
     * @param top
     * @return
     */
    public CategoryCount getCategoryCount(int top) {

        SQLiteCursor cursor = null;
        CategoryCount categoryCount = null;

        // @formatter:off
        String sQuery = 
            " SELECT COUNT(" + Columns.ROW_ID + ") " +
            "      , SUM( CASE " + Columns.CATEGORY_CD + " WHEN " + Const.CATEGORY.GRAPP + " THEN 1 ELSE 0 END ) " + 
            "      , SUM( CASE " + Columns.CATEGORY_CD + " WHEN " + Const.CATEGORY.PROX + " THEN 1 ELSE 0 END ) " + 
            "      , SUM( CASE " + Columns.CATEGORY_CD + " WHEN " + Const.CATEGORY.SHOOT + " THEN 1 ELSE 0 END ) " + 
            "      , SUM( CASE " + Columns.CATEGORY_CD + " WHEN " + Const.CATEGORY.CANNON + " THEN 1 ELSE 0 END ) " + 
            "      , SUM( CASE " + Columns.CATEGORY_CD + " WHEN " + Const.CATEGORY.SNIPE + " THEN 1 ELSE 0 END ) " + 
            "   FROM ( " + 
            "        SELECT " + Columns.ROW_ID + 
            "             , " + Columns.CATEGORY_CD + 
            "          FROM " + TABLE_NAME;
        
        if (top > 0){
            sQuery = sQuery + 
            "         ORDER BY " + Columns.ROW_ID + " DESC" + 
            "         LIMIT " + top;
        }
            
        sQuery = sQuery + " ) ";
        // @formatter:on

        try {

            cursor = (SQLiteCursor) mDatabase.rawQuery(sQuery, null);
            cursor.moveToFirst();

            if (cursor.getCount() > 0) {

                categoryCount = new CategoryCount();

                categoryCount.setTotal(cursor.getInt(0));
                categoryCount.setGrapp(cursor.getInt(1));
                categoryCount.setProx(cursor.getInt(2));
                categoryCount.setShoot(cursor.getInt(3));
                categoryCount.setCannon(cursor.getInt(4));
                categoryCount.setSnipe(cursor.getInt(5));

            }

        } catch (Exception e) {

            Log.e(TAG, "getCategoryCount failed - " + e.toString());

        } finally {

            if (cursor != null) {
                cursor.close();
            }

        }

        return categoryCount;

    }

    /**
     * 特定レコードの削除
     * 
     * @param rowId
     * @return 処理件数
     */
    public int deleteAt(int rowId) {

        long lDelete = 0;

        // @formatter:off
        String sWhere = Columns.ROW_ID + " = " + rowId;
        // @formatter:on

        try {

            lDelete = mDatabase.delete(TABLE_NAME, sWhere, null);

        } catch (Exception e) {

            Log.e(TAG, "deleteAt failed - " + e.toString());

        }

        return (int) lDelete;
    }

}
